﻿
/*
 * Create table UDS_MailGroup
 */
IF ( OBJECT_ID(N'dbo.UDS_MailGroup', N'U') IS NULL )
BEGIN
    CREATE TABLE dbo.UDS_MailGroup
    (
        GroupID     int             NOT NULL IDENTITY(1,1),
        GroupName   nvarchar(255)   COLLATE Chinese_PRC_CI_AS NOT NULL,
        Owner       nvarchar(50)    COLLATE Chinese_PRC_CI_AS NOT NULL,
        TimeCreated datetime        NOT NULL CONSTRAINT [DF_UDS_MailGroup_TimeCreated] DEFAULT(getdate()),

        CONSTRAINT [PK_UDS_MailGroup] PRIMARY KEY NONCLUSTERED ( GroupID asc ),
        CONSTRAINT [FK_UDS_MailGroup_Owner] FOREIGN KEY ([Owner]) REFERENCES dbo.UDS_Staff(Staff_Name)
    );
END
GO

-- Create an UNIQUE constraint on GroupName column
IF EXISTS (SELECT 1 FROM sys.key_constraints WHERE parent_object_id = object_id(N'[dbo].[UDS_MailGroup]') and [name] = N'UQ_UDS_MailGroup_GroupName')
BEGIN
    ALTER TABLE [dbo].[UDS_MailGroup] DROP CONSTRAINT [UQ_UDS_MailGroup_GroupName];
END

ALTER TABLE [dbo].[UDS_MailGroup] ADD CONSTRAINT [UQ_UDS_MailGroup_GroupName] UNIQUE CLUSTERED
(
    [GroupName] asc
);
GO
